﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DBLibrary
{
    public class DBHandler
    {
        // Provide the following information
        private static string userName = "wpbgu@vfx4577tbr";
        private static string password = "Bennyyair2012";
        private static string dataSource = "tcp:vfx4577tbr.database.windows.net,1433";
        private static string databaseName = "TravelDB";
        SqlConnection conn;

        public DBHandler()
        {
            //Server=tcp:vfx4577tbr.database.windows.net,1433;Database=TravelDB;User ID=wpbgu@vfx4577tbr;Password={your_password_here};Trusted_Connection=False;Encrypt=True;Connection Timeout=30;
            // Create a connection string for the sample database
            SqlConnectionStringBuilder connString;
            connString = new SqlConnectionStringBuilder();
            connString.DataSource = dataSource;
            connString.InitialCatalog = databaseName;
            connString.Encrypt = true;
            connString.TrustServerCertificate = false;
            connString.UserID = userName;
            connString.Password = password;

            // Connect to the sample database and perform various operations
            conn = new SqlConnection(connString.ToString());
            SqlCommand command = conn.CreateCommand();
            conn.Open();

            // Create a table
            command.CommandText = "CREATE TABLE T1(Col1 int primary key, Col2 varchar(20))";
            command.ExecuteNonQuery();

            // Insert sample records
            command.CommandText = "INSERT INTO T1 (col1, col2) values (1, 'string 1'), (2, 'string 2'), (3, 'string 3')";
            int rowsAdded = command.ExecuteNonQuery();

            // Query the table and print the results
            command.CommandText = "SELECT * FROM T1";

           SqlDataReader reader = command.ExecuteReader();
            // Loop over the results
            while (reader.Read())
            {
                Console.WriteLine("Col1: {0}, Col2: {1}",
                                reader["Col1"].ToString().Trim(),
                                reader["Col2"].ToString().Trim());
            }
            reader.Close();
            // Update a record
            command.CommandText = "UPDATE T1 SET Col2='string 1111' WHERE Col1=1";
            command.ExecuteNonQuery();

            // Delete a record
            command.CommandText = "DELETE FROM T1 WHERE Col1=2";
            command.ExecuteNonQuery();

            // Query the table and print the results

            Console.WriteLine("\nAfter update/delete the table has these records...");

            command.CommandText = "SELECT * FROM T1";

             SqlDataReader reader2 = command.ExecuteReader();
            // Loop over the results
            while (reader2.Read())
            {
                Console.WriteLine("Col1: {0}, Col2: {1}",
                                reader2["Col1"].ToString().Trim(),
                                reader2["Col2"].ToString().Trim());
            }
                   
            conn.Close();              
            
            Console.WriteLine("Press enter to continue...");
            Console.ReadLine();
        }

        public string register(string username, string password, string fName, string lName, string extra)
        {
            SqlCommand command = conn.CreateCommand();
            conn.Open();

            // Create new user
            string commandStr = "INSERT INTO TMusers VALUES ('" + "" + "','" + lName + "','" + fName + 
                "'," + password + ",'" + username + "',NULL,NULL,NULL)";
            command.CommandText = commandStr;
            int rowsEffected = command.ExecuteNonQuery();
            conn.Close();
            if (rowsEffected == 0)
                return null;

            return null;
        }

        public string login(string username, string password)
        {
            string ans = null;
            SqlCommand command = conn.CreateCommand();
            conn.Open();

            string commandStr = "SELECT * FROM TMusers WHERE Username like '"+ username + "'";
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            // Loop over the results
            if (reader.Read())
                if (reader["Password"].ToString().Equals(password))
                    ans = "OK";
            reader.Close();
            conn.Close();

            return ans;
        }

        public string retrieveTrack(string trackName)
        {
            string ans = null;
            SqlCommand command = conn.CreateCommand();
            conn.Open();
            string commandStr = "SELECT * FROM TMtracks WHERE Name like '" + trackName + "'";
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
                    ans = "OK";
            reader.Close();
            conn.Close();

            return ans;
        }

        public string searchByLocation(string area, string latitude, string longitude)
        {
            string ans = null;
            SqlCommand command = conn.CreateCommand();
            conn.Open();
            string commandStr = "SELECT * FROM TMtracks WHERE Latitude = " + latitude + " AND Longitude = " + longitude;
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
                ans = reader["Name"].ToString();
            reader.Close();
            conn.Close();

            return ans;
        }

        public string retrieveTracksByLocation(string latitude, string longitude)
        {
            string ans = "";
            SqlCommand command = conn.CreateCommand();
            conn.Open();
            string commandStr = "SELECT * FROM TMtracks WHERE Latitude >= " + latitude + " AND Longitude >= " + longitude;
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
                ans += reader["Name"].ToString() + ";";
            reader.Close();
            conn.Close();

            return "".Equals(ans) ? null : ans;
        }

        public string retrieveTrackReports(int trackId)
        {
            string ans = "";
            SqlCommand command = conn.CreateCommand();
            conn.Open();
            string commandStr = "SELECT * FROM TMreports WHERE TrackId like =" + trackId;
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
                ans += reader["Content"].ToString();
            reader.Close();
            conn.Close();

            return "".Equals(ans) ? null : ans;
        }

        public string getReportsByUser(string userId, string trackId)
        {
            string ans = "";
            SqlCommand command = conn.CreateCommand();
            conn.Open();
            string commandStr = "SELECT * FROM TMreports WHERE UserId like '" + userId + "'";
            command.CommandText = commandStr;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
                ans += reader["Content"].ToString();
            reader.Close();
            conn.Close();

            return "".Equals(ans) ? null : ans;
        }

        static void Main(string[] args){

            DBHandler c  = new DBHandler();
            c.login("benny", "1234");
            c.retrieveTrackReports(1);

        }
    }
}
